﻿Imports System.Data
Imports System.Data.SqlClient
Imports System.Text.RegularExpressions
Imports System.Text
Imports System.Linq
Imports System.Data.DataTableExtensions
Imports System.Data.DataRowExtensions
Imports System.IO


'Database connectieklasse
Public Class Connection
    Dim conn As SqlConnection
    Dim cmd As SqlCommand

    'Constructor
#Region "Singleton Implementatie"

    Private Shared instance As Connection

    Protected Sub New()
    End Sub

    Public Shared Function getInstance() As Connection
        If instance Is Nothing Then
            instance = New Connection
        End If
        getInstance = instance
    End Function
#End Region

#Region "Aanmaken Database, Tabellen, Views en Stored Procedures"
    'Aanmaken Database
    Sub CreateDB()
        Try
            Using conn As New SqlConnection(My.Settings.MasterInstanceConnectionString)
                conn.Open()
                Using cmd As New SqlCommand
                    'Aanmaken Database
                    cmd.Connection = conn
                    cmd.CommandText = My.Resources.Scripts.CreateDBSimple
                    cmd.ExecuteNonQuery()
                    PrintMsg("DB Aanmaken OK")

                    'Aanmaken Tabellen
                    cmd.CommandText = My.Resources.Scripts.CreateTables
                    cmd.ExecuteNonQuery()
                    PrintMsg("Tabellen Aanmaken OK")

                    'Aanmaken Stored Procedures
                    Dim commands() As String = Nothing
                    commands = ParseScriptToCommands(My.Resources.Scripts.Procedures)
                    For Each command As String In commands
                        If command.Trim().Length > 0 Then
                            cmd.CommandText = command
                            cmd.ExecuteNonQuery()
                        End If
                    Next
                    PrintMsg("Stored Procedures Aanmaken OK")

                    'Aanmaken Views
                    commands = Nothing
                    commands = ParseScriptToCommands(My.Resources.Scripts.Views)
                    For Each command As String In commands
                        If command.Trim().Length > 0 Then
                            cmd.CommandText = command
                            cmd.ExecuteNonQuery()
                        End If
                    Next
                    PrintMsg("Views Aanmaken OK")

                    'DB vullen met standaard waarden
                    cmd.CommandText = My.Resources.Scripts.InsertData
                    cmd.ExecuteNonQuery()
                    PrintMsg("Default Data Insert OK")
                End Using
                conn.Close()
                SqlConnection.ClearAllPools()
            End Using
        Catch sqlExc As SqlException
            Throw sqlExc
        Catch ex As Exception
            Throw ex
        End Try
    End Sub

#End Region

#Region "Get Stored Procedure- en View DataSet"
    'Functie die de dataset van een Stored Procedure teruggeeft

    'Geen Parameters
    Function GetStoredProcedure(ByVal sp As String) As DataSet
        Dim adapter As SqlDataAdapter
        Dim ds As New DataSet

        Using conn As New SqlConnection(My.Settings.ConnectionString)
            Dim scmd As New SqlCommand(sp, conn)
            scmd.CommandType = CommandType.StoredProcedure
            adapter = New SqlDataAdapter(scmd)
            Try
                adapter.Fill(ds)
            Catch expSQL As SqlException
                Throw expSQL
                GetStoredProcedure = Nothing
                Exit Function
            End Try
        End Using
        GetStoredProcedure = ds
    End Function

    'meerdere parameters
    Function GetStoredProcedure(ByVal sp As String, ByVal inputParams As ArrayList) As DataSet
        Dim adapter As SqlDataAdapter
        Dim ds As New DataSet
        Dim iter As Integer = 0

        Using conn As New SqlConnection(My.Settings.ConnectionString)
            Dim scmd As New SqlCommand(sp, conn)
            scmd.CommandType = CommandType.StoredProcedure
            adapter = New SqlDataAdapter(scmd)

            'haalt de parameters uit de StoredProcedure via de functie GetParametersOfSP
            'Voegt de waarden v [ArrayList inputParams] toe aan de parameters v [SqlCommand scmd] 
            For Each parameter As SqlParameter In GetParametersOfSP(scmd)
                If (parameter.Direction = ParameterDirection.Input) Then
                    scmd.Parameters.Item(parameter.ParameterName).Value = inputParams(iter)
                    iter += 1
                End If
            Next

            '[DataSet ds] vullen
            Try
                adapter.Fill(ds)
            Catch expSQL As SqlException
                Throw expSQL
                GetStoredProcedure = Nothing
                Exit Function
            End Try
        End Using
        GetStoredProcedure = ds
    End Function

#End Region

#Region "Get List of Names of Stored Procedures / Views"
    Function GetListOf(ByVal objectName As String) As DataSet
        Dim ds As New DataSet
        Dim iter As Integer = 0
        Dim StoredProcedure As String = "Get_" & objectName


        ds = GetStoredProcedure(StoredProcedure)

        GetListOf = ds

    End Function

    'Functie die de input en output parameters achterhaalt v/e Stored Procedure
    '[SqlCommand command] moet geladen worden in de functie/sub waar GetParametersOfSP opgeroepen wordt
    Function GetParametersOfSP(ByVal command As SqlCommand) As SqlParameterCollection


        conn = command.Connection
        conn.Open()
        SqlCommandBuilder.DeriveParameters(command)
        conn.Close()

        GetParametersOfSP = command.Parameters
    End Function
#End Region

#Region "Execute SQL file"
    Public Sub ExecuteSQLFile(ByVal path As String)
        Dim script As String
        script = File.OpenText("C:\map.sql").ReadToEnd()

        script = script.Replace("`", "")

        Using conn As New SqlConnection(My.Settings.ConnectionString)
            Using cmd As New SqlCommand("map.sql", conn)
                Try
                    conn.Open()
                    cmd.CommandText = script
                    cmd.ExecuteNonQuery()
                    conn.Close()
                Catch ex As Exception
                    Throw ex
                End Try
            End Using
        End Using
    End Sub
#End Region

#Region "Verwerking SQL scripts naar commando's"
    'Deze functie zorgt vr een array met enkel sql commando's
    Private Function ParseScriptToCommands(ByVal script As String) As String()
        Dim commands() As String
        commands = Regex.Split(script, _
                                "GO\r\n", _
                                RegexOptions.IgnoreCase)
        Return commands
    End Function
#End Region
#Region "Print Output in cmd vr testing"
    Sub PrintMsg(ByVal msg As String)
        Console.WriteLine(msg)
    End Sub
#End Region
End Class

